A microfiche appendix having 1 microfiche and 46 frames is included herewith containing the detailed design specification for the present invention.
1. Field of the Invention
The present invention is directed to a system, method and data structure for database access, creation and maintenance through a two-dimensional database partitioning process. More particularly, the present invention is directed to a system which reduces access, backup, and processing time required by partitioning data in a database by partition group and then further partitioning each partition group by event processing date into two-dimensional partitions in the form of rolling tables.
2. Description of the Related Art
Many industries, but specifically telecommunications providers, need to maintain extremely large databases. Such databases contain customer records, price plans, as well as CDRs (xe2x80x9cCall Detail Recordxe2x80x9d). CDRs are logged by network elements (i.e. telephone switches), where they are temporarily kept prior to being transferred to a database at the earliest opportunity. The rating and billing system uses the CDRs stored in the database for event rating, pricing and billing. The more customers and the higher the call volume, the larger the amount of data in the database. The larger the database partition, the slower the access to the data stored. Database access is typically the slowest part of the whole telecommunications computer system. Therefore, improvements in this area result in improvements of response time to the computer users.
No less important than response time are maintenance issues. The database size can reach a point where it is no longer feasible to do a backup in an off-peak period (e.g. at night), as this would take too long. Yet backups are key to data security and hence revenue. It should be noted that in order to preserve integrity and consistency, data shouldn""t be modified during the backup process. Therefore, keeping backup time to a minimum is highly desirable.
Further, outside of the backup time required for modifications of large data volumes such modifications should also be avoided in a database for performance reasons. Data modifications is one of the most time consuming transactions compared to other database operations.
Database access time is of extreme importance to telecommunications firms, and other industries, due to the competitive nature of the industry. In the present highly competitive market, the telecommunication providers must offer new services such as real time rating and on demand bills. Both features require faster access to data stored in the database. This requirement conflicts directly with the above issues.
A large system in a mission critical environment needs to support several capabilities discussed below.
The ability to handle large volumes of data such as 30 million usage events per day minimum. Data needs to be stored online for up to 90 days in most applications.
As part of what has come to be called xe2x80x9cReal Time Ratingxe2x80x9d, events must be available in the system in a timely fashion for display so that the CSR would be able to access the latest events should a customer call.
It must be possible to back up the database in a reasonable period of time. The industry standard is nightly backups for new or modified data.
It must be possible for the database to be restored in a timely fashion in case of problems (e.g. disaster recovery, data corruption, database layout changes, etc.). For database layout changes backup and recovery must be able to fit into a single weekend, or other idle period to allow for normal production activities.
Further, fast deletion of old records such as those over 60-90 days old needs to be possible.
A high degree of flexibility is also required. As part of this flexibility it must be possible to change bill cycles and support billing xe2x80x9con demandxe2x80x9d. A variation of the latter is providing customer bills online so that at any point in the billing cycle the customer can check his balance to-date.
Prior to the present invention no system existed that could accomplish all the foregoing features for systems needing to process and retain extremely large volumes of data. Single dimension database partitioning is common and widely used in industry. However, no prior art is known similar to the two-dimensional partitioning through a combination of marking events and two-dimensional partitions in the form of rolling tables.
It is an object of the present invention provide a system and method that reduces access, backup, and processing time required by partitioning data in a database by partition group and then further partitioning the partition group by event processing date data into two-dimensional partitions in the form of rolling tables.
It is a further object of the present invention to handle large volumes of data and store this data online for a predetermined period of time so that the customers can query their charges and the CSR can access a customer""s data quickly.
It is also an object of the present invention to support on demand billing, which when linked together with real time rating provides for a timely fashion to display data so that the CSR would be able to access the latest events should a customer call.
It is still a further object of the present invention to back up the database in a reasonable period of time, such as nightly backups, for new or modified data.
It is also an object of the present invention to restore the database in a timely fashion in case of problems (e.g. disaster recovery, data corruption, database layout changes, etc.). For database layout changes, backup and recovery must be able to fit into a single weekend, or other idle period to allow for normal production activities.
It is also an object of the present invention to quickly delete old records.
It is a further object of the present invention to afford a high degree of flexibility to enable to change bill cycles.
It is a still further object of the present invention to provide customer bills online so that at any point in the billing cycle the customer can check his balance to-date.
It is a further object of the present invention to minimize processing time required to access a database and thereby reduce the load on a server.
The above objects can be attained by a system, method and data structure that a system which reduces access, backup, and processing time required by partitioning data in a database by partition group and then further partition each partition group by event processing date into several two-dimensional partitions in the form of rolling tables.
These together with other objects and advantages which will be subsequently apparent, reside in the details of construction and operation as more fully hereinafter described and claimed, reference being had to the accompanying drawings forming a part hereof, wherein like numerals refer to like parts throughout.